package com.zhuozhengsoft.pageoffice6springboot2back.controller;

import com.zhuozhengsoft.pageoffice.OpenModeType;
import com.zhuozhengsoft.pageoffice.PageOfficeCtrl;
import com.zhuozhengsoft.pageoffice.excel.*;
import com.zhuozhengsoft.pageoffice6springboot2back.util.GetDirPathUtil;
import org.json.JSONObject;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

@RestController
@RequestMapping(value = "/DefinedNameTable/")
public class DefinedNameTableController {
    //获取doc目录的磁盘路径
    private final String dir = GetDirPathUtil.getDirPath() + "/static/doc/";
    @RequestMapping(value = "Excel")
    public String showWord(HttpServletRequest request) {
        PageOfficeCtrl poCtrl = new PageOfficeCtrl(request);
        //定义Workbook对象
        WorkbookWriter workBook = new WorkbookWriter();
        //定义Sheet对象，"Sheet1"是打开的Excel表单的名称
        SheetWriter sheet = workBook.openSheet("Sheet1");
        ExcelTableWriter table = sheet.openTableByDefinedName("report", 10, 5, false);
        table.getDataFields().get(0).setValue("轮胎");
        table.getDataFields().get(1).setValue("100");
        table.getDataFields().get(2).setValue("120");
        table.getDataFields().get(3).setValue("500");
        table.getDataFields().get(4).setValue("120%");
        table.nextRow();
        table.close();
        poCtrl.setWriter(workBook);

        poCtrl.webOpen("/doc/DefinedNameTable/test.xlsx", OpenModeType.xlsSubmitForm, "张三");
        return poCtrl.getHtml();
    }

    @RequestMapping(value = "Excel2")
    public String showWord2(HttpServletRequest request, String tempFileName) {
        PageOfficeCtrl poCtrl = new PageOfficeCtrl(request);
        //定义Workbook对象
        WorkbookWriter workBook = new WorkbookWriter();
        //定义Sheet对象，"Sheet1"是打开的Excel表单的名称
        SheetWriter sheet = workBook.openSheet("Sheet1");
        //定义Table对象，参数“report”就是Excel模板中定义的单元格区域的名称
        ExcelTableWriter table = sheet.openTableByDefinedName("report", 10, 5, true);
        //给区域中的单元格赋值
        table.getDataFields().get(0).setValue("轮胎");
        table.getDataFields().get(1).setValue("100");
        table.getDataFields().get(2).setValue("120");
        table.getDataFields().get(3).setValue("500");
        table.getDataFields().get(4).setValue("120%");
        //循环下一行
        table.nextRow();
        //关闭table对象
        table.close();
        //定义单元格对象，参数“year”就是Excel模板中定义的单元格的名称
        ExcelCellWriter cellYear = sheet.openCellByDefinedName("year");
        // 给单元格赋值
        Calendar c = new GregorianCalendar();
        int year = c.get(Calendar.YEAR);//获取年份
        cellYear.setValue(year + "年");
        ExcelCellWriter cellName = sheet.openCellByDefinedName("name");
        cellName.setValue("张三");
        poCtrl.setWriter(workBook);

        poCtrl.webOpen("/doc/DefinedNameTable/" + tempFileName, OpenModeType.xlsSubmitForm, "张三");
        return poCtrl.getHtml();
    }

    @RequestMapping(value = "Excel6")
    public String showWord6(HttpServletRequest request) {
        PageOfficeCtrl poCtrl = new PageOfficeCtrl(request);
        poCtrl.webOpen("/doc/DefinedNameTable/test4.xlsx", OpenModeType.xlsNormalEdit, "张三");
        return poCtrl.getHtml();
    }

    @RequestMapping(value = "Excel4")
    public String showWord4(HttpServletRequest request) {
        PageOfficeCtrl poCtrl = new PageOfficeCtrl(request);
        //定义Workbook对象
        WorkbookWriter workBook = new WorkbookWriter();
        //定义Sheet对象，"Sheet1"是打开的Excel表单的名称
        SheetWriter sheet = workBook.openSheet("Sheet1");

        //定义Table对象
        ExcelTableWriter table = sheet.openTable("B4:F11");

        int rowCount = 12;//假设将要自动填充数据的实际记录条数为12
        for (int i = 1; i <= rowCount; i++) {
            //给区域中的单元格赋值
            table.getDataFields().get(0).setValue(i + "月");
            table.getDataFields().get(1).setValue("100");
            table.getDataFields().get(2).setValue("120");
            table.getDataFields().get(3).setValue("500");
            table.getDataFields().get(4).setValue("120%");
            table.nextRow();//循环下一行，此行必须
        }

        //关闭table对象
        table.close();

        //定义Table对象
        ExcelTableWriter table2 = sheet.openTable("B13:F16");
        int rowCount2 = 4;//假设将要自动填充数据的实际记录条数为12
        for (int i = 1; i <= rowCount2; i++) {
            //给区域中的单元格赋值
            table2.getDataFields().get(0).setValue(i + "季度");
            table2.getDataFields().get(1).setValue("300");
            table2.getDataFields().get(2).setValue("300");
            table2.getDataFields().get(3).setValue("300");
            table2.getDataFields().get(4).setValue("100%");
            table2.nextRow();
        }

        //关闭table对象
        table2.close();
        poCtrl.setWriter(workBook);

        poCtrl.webOpen("/doc/DefinedNameTable/test4.xlsx", OpenModeType.xlsNormalEdit, "张三");
        return poCtrl.getHtml();
    }

    @RequestMapping(value = "Excel5")
    public String showWord5(HttpServletRequest request) {
        PageOfficeCtrl poCtrl = new PageOfficeCtrl(request);
        //定义Workbook对象
        WorkbookWriter workBook = new WorkbookWriter();
        //定义Sheet对象，"Sheet1"是打开的Excel表单的名称
        SheetWriter sheet = workBook.openSheet("Sheet1");

        //定义Table对象，参数“report1”为Excel中定义的名称，“4”为名称指定区域的行数，
        //“5”为名称指定区域的列数，“true”表示表格会按实际数据行数自动扩展
        ExcelTableWriter table = sheet.openTableByDefinedName("report", 4, 5, true);

        int rowCount = 12;//假设将要自动填充数据的实际记录条数为12
        for (int i = 1; i <= rowCount; i++) {
            //给区域中的单元格赋值
            table.getDataFields().get(0).setValue(i + "月");
            table.getDataFields().get(1).setValue("100");
            table.getDataFields().get(2).setValue("120");
            table.getDataFields().get(3).setValue("500");
            table.getDataFields().get(4).setValue("120%");
            table.nextRow();//循环下一行，此行必须
        }

        //关闭table对象
        table.close();

        //定义Table对象
        ExcelTableWriter table2 = sheet.openTableByDefinedName("report2", 4, 5, true);
        int rowCount2 = 4;//假设将要自动填充数据的实际记录条数为12
        for (int i = 1; i <= rowCount2; i++) {
            //给区域中的单元格赋值
            table2.getDataFields().get(0).setValue(i + "季度");
            table2.getDataFields().get(1).setValue("300");
            table2.getDataFields().get(2).setValue("300");
            table2.getDataFields().get(3).setValue("300");
            table2.getDataFields().get(4).setValue("100%");
            table2.nextRow();
        }
        //关闭table对象
        table2.close();
        poCtrl.setWriter(workBook);

        poCtrl.webOpen("/doc/DefinedNameTable/test4.xlsx", OpenModeType.xlsNormalEdit, "张三");
        return poCtrl.getHtml();
    }


    @RequestMapping("save")
    public void save(HttpServletRequest request, HttpServletResponse response) throws IOException {
        WorkbookReader workBook = new WorkbookReader(request, response);
        SheetReader sheet = workBook.openSheet("Sheet1");
        ExcelTableReader table = sheet.openTableByDefinedName("report");

        List<JSONObject> objectList=new ArrayList<>();
        while (!table.getEOF()) {
            //获取提交的数值
            if (!table.getDataFields().getIsEmpty()) {
                /**
                 * 实际开发中，一般获取数据区域的值后用来和数据库进行交互，比如根据刚才获取的数据进行数据库记录的新增，更新，删除等。
                 * 此处为了给用户展示获取的数据内容，通过setCustomSaveResult将获取的数据区域的值返回到前端页面给用户检查执行的结果。
                 * 如果只是想返回一个保存结果，可以使用比如：setCustomSaveResult("ok")，前端可以根据这个保存结果进行下一步逻辑处理。
                 */
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("名称",table.getDataFields().get(0).getText());
                jsonObject.put("计划完成量", table.getDataFields().get(1).getText());
                jsonObject.put("实际完成量", table.getDataFields().get(2).getText());
                jsonObject.put("累计完成量", table.getDataFields().get(3).getText());
                float f = Float.parseFloat(table.getDataFields().get(2).getText());
                  f = f / Float.parseFloat(table.getDataFields().get(1).getText());
                  DecimalFormat df = (DecimalFormat) NumberFormat.getInstance();
                jsonObject.put("完成率",  df.format(f * 100) + "%");
                objectList.add(jsonObject);
            }
            //循环进入下一行
            table.nextRow();
        }
        table.close();
        workBook.setCustomSaveResult(objectList.toString());//通过setCustomSaveResult给前端页面返回数据
        workBook.close();//必须，并且放最后一行

    }
}
